Oracle – spool sql to file with on the fly gzip compression

To run SQL dumps through gzip compression on the fly

1) Create a named pipe to destination for the sql spool using this command:

mknod output.pipe p

2) run a process in the background to compress any data sent to the named pipe

nohup gzip -c < output.pipe > output.gz &

3) Set up your SQL to spool to the named pipe

set feedback off
set heading off
set pagesize 0
set linesize 2000
set space 0
set newpage 0
set verify off
set termout off
set echo off
set wrap off
set flush off
set trimspool on
spool ‘output.pipe’;
select XXXX from XXXX
spool off;
exit;

4) Run the SQL and the output will be zipped up as it is created and put in the output.gz file…

This entry was posted in Uncategorized. Bookmark the permalink.